Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient

Namespace sk.DAL.SQLProviders
    Public Class SQLArticleProvider
        Inherits ArticleProvider

        Public Overrides Function ChangeArticleApproval(ByVal ArticleID As Integer, ByVal status As Boolean) As Boolean
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("Article_ChangeArticleApproval", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    Dim pArticleID As New SqlParameter("@ID", SqlDbType.SmallInt)
                    pArticleID.Value = ArticleID
                    Dim pStatus As New SqlParameter("@Approve", SqlDbType.Bit)
                    pStatus.Value = status
                    cmd.Parameters.Add(pArticleID)
                    cmd.Parameters.Add(pStatus)
                    con.Open()
                    cmd.ExecuteNonQuery()
                    Return True
                End Using
            Catch ex As Exception
                Return False
            End Try
        End Function

        Public Overrides Function CreateArticle(ByVal Atcle As Article) As Boolean
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("Article_CreateArticle", con)
                    cmd.CommandType = CommandType.StoredProcedure

                    Dim pArticleID As New SqlParameter("@ArticleID", SqlDbType.SmallInt)
                    pArticleID.Value = GetNextID()
                    Dim pUserID As New SqlParameter("@UserID", SqlDbType.NVarChar)
                    pUserID.Value = Atcle.UserID
                    Dim pTitle As New SqlParameter("@Title", SqlDbType.NVarChar)
                    pTitle.Value = Atcle.Title
                    Dim pText As New SqlParameter("@Text", SqlDbType.Text)
                    pText.Value = Atcle.Text
                    Dim pCreationDate As New SqlParameter("@CreationDate", SqlDbType.SmallDateTime)
                    pCreationDate.Value = Atcle.CreationDate
                    Dim pPostDate As New SqlParameter("@PostDate", SqlDbType.SmallDateTime)
                    pPostDate.Value = Atcle.PostDate
                    Dim pExpiredate As New SqlParameter("@Expiredate", SqlDbType.SmallDateTime)
                    pExpiredate.Value = Atcle.ExpirationDate
                    Dim pApproved As New SqlParameter("@Approved", SqlDbType.Bit)
                    pApproved.Value = Atcle.Approved
                    Dim pCategoryID As New SqlParameter("@CategoryID", SqlDbType.SmallInt)
                    pCategoryID.Value = Atcle.CategoryID

                    cmd.Parameters.Add(pArticleID)
                    cmd.Parameters.Add(pUserID)
                    cmd.Parameters.Add(pTitle)
                    cmd.Parameters.Add(pText)
                    cmd.Parameters.Add(pCreationDate)
                    cmd.Parameters.Add(pPostDate)
                    cmd.Parameters.Add(pExpiredate)
                    cmd.Parameters.Add(pApproved)
                    cmd.Parameters.Add(pCategoryID)

                    con.Open()
                    cmd.ExecuteNonQuery()
                    Return True
                End Using
            Catch ex As Exception
                Return False
            End Try
        End Function

        Public Overrides Function DeleteArticle(ByVal ArticleID As Integer) As Boolean
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("Article_DeleteArticle", con)
                    cmd.CommandType = CommandType.StoredProcedure

                    Dim pArticleID As New SqlParameter("@ID", SqlDbType.SmallInt)
                    pArticleID.Value = ArticleID
                    cmd.Parameters.Add(pArticleID)

                    con.Open()
                    cmd.ExecuteNonQuery()
                    Return True
                End Using
            Catch ex As Exception
                Return False
            End Try
        End Function

        Public Overrides Function GetAllArticles() As System.Collections.Generic.List(Of Article)
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("Article_GetAllArticles", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    con.Open()

                    Dim reader As IDataReader = cmd.ExecuteReader()
                    Return GetArticleCollectionFromReader(reader)

                End Using
            Catch ex As Exception
                Return New System.Collections.Generic.List(Of Article)
            End Try
        End Function

        Public Overrides Function GetArticleByID(ByVal AtcleID As Integer) As Article
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("Article_GetArticleByID", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    Dim pArticleID As New SqlParameter("@ID", SqlDbType.SmallInt)
                    pArticleID.Value = AtcleID
                    cmd.Parameters.Add(pArticleID)
                    con.Open()

                    Dim reader As SqlDataReader = cmd.ExecuteReader()
                    reader.Read()

                    Dim newArticle As New Article

                    newArticle.ArticleID = CInt(reader.GetValue(0))
                    newArticle.UserID = reader.GetString(1)
                    newArticle.Title = reader.GetString(2)
                    newArticle.Text = reader.GetString(3)
                    newArticle.CreationDate = reader.GetDateTime(4)
                    newArticle.PostDate = reader.GetDateTime(5)
                    newArticle.ExpirationDate = reader.GetDateTime(6)
                    newArticle.Approved = reader.GetBoolean(7)
                    newArticle.CategoryID = CInt(reader.GetValue(8))


                    Return newArticle
                End Using
            Catch ex As Exception
                Return New Article
            End Try
        End Function

        Public Overrides Function UpdateArticle(ByVal Atcle As Article) As Boolean
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("Article_UpdateArticle", con)
                    cmd.CommandType = CommandType.StoredProcedure

                    Dim pArticleID As New SqlParameter("@ArticleID", SqlDbType.SmallInt)
                    pArticleID.Value = Atcle.ArticleID
                    Dim pUserID As New SqlParameter("@UserID", SqlDbType.NVarChar)
                    pUserID.Value = Atcle.UserID
                    Dim pTitle As New SqlParameter("@Title", SqlDbType.NVarChar)
                    pTitle.Value = Atcle.Title
                    Dim pText As New SqlParameter("@Text", SqlDbType.Text)
                    pText.Value = Atcle.Text
                    Dim pCreationDate As New SqlParameter("@CreationDate", SqlDbType.SmallDateTime)
                    pCreationDate.Value = Atcle.CreationDate
                    Dim pPostDate As New SqlParameter("@PostDate", SqlDbType.SmallDateTime)
                    pPostDate.Value = Atcle.PostDate
                    Dim pExpiredate As New SqlParameter("@Expiredate", SqlDbType.SmallDateTime)
                    pExpiredate.Value = Atcle.ExpirationDate
                    Dim pApproved As New SqlParameter("@Approved", SqlDbType.Bit)
                    pApproved.Value = Atcle.Approved
                    Dim pCategoryID As New SqlParameter("@CategoryID", SqlDbType.SmallInt)
                    pCategoryID.Value = Atcle.CategoryID

                    cmd.Parameters.Add(pArticleID)
                    cmd.Parameters.Add(pUserID)
                    cmd.Parameters.Add(pTitle)
                    cmd.Parameters.Add(pText)
                    cmd.Parameters.Add(pCreationDate)
                    cmd.Parameters.Add(pPostDate)
                    cmd.Parameters.Add(pExpiredate)
                    cmd.Parameters.Add(pApproved)
                    cmd.Parameters.Add(pCategoryID)

                    con.Open()
                    cmd.ExecuteNonQuery()
                    Return True
                End Using
            Catch ex As Exception
                Return False
            End Try
        End Function

        Public Overrides Function UpdateArticleText(ByVal ArticleId As Integer, ByVal updatedText As String) As Boolean
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("Article_UpdateArticleText", con)
                    cmd.CommandType = CommandType.StoredProcedure

                    Dim pArticleID As New SqlParameter("@ID", SqlDbType.SmallInt)
                    pArticleID.Value = ArticleId
                    Dim pText As New SqlParameter("@Text", SqlDbType.Text)
                    pText.Value = updatedText


                    cmd.Parameters.Add(pArticleID)
                    cmd.Parameters.Add(pText)

                    con.Open()
                    cmd.ExecuteNonQuery()
                    Return True
                End Using
            Catch ex As Exception
                Return False
            End Try
        End Function

        Public Overrides Function UpdateExpireDate(ByVal ArticleID As Integer, ByVal newExpireDate As Date) As Boolean
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("Article_UpdateExpireText", con)
                    cmd.CommandType = CommandType.StoredProcedure

                    Dim pArticleID As New SqlParameter("@ID", SqlDbType.SmallInt)
                    pArticleID.Value = ArticleID
                    Dim pExpireDate As New SqlParameter("@Date", SqlDbType.SmallDateTime)
                    pExpireDate.Value = newExpireDate


                    cmd.Parameters.Add(pArticleID)
                    cmd.Parameters.Add(pExpireDate)

                    con.Open()
                    cmd.ExecuteNonQuery()
                    Return True
                End Using
            Catch ex As Exception
                Return False
            End Try
        End Function

        Public Overrides Function UpdatePostDate(ByVal ArticleID As Integer, ByVal newPostDate As Date) As Boolean
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("Article_UpdatePostText", con)
                    cmd.CommandType = CommandType.StoredProcedure

                    Dim pArticleID As New SqlParameter("@ID", SqlDbType.SmallInt)
                    pArticleID.Value = ArticleID
                    Dim pPostDate As New SqlParameter("@Date", SqlDbType.SmallDateTime)
                    pPostDate.Value = newPostDate


                    cmd.Parameters.Add(pArticleID)
                    cmd.Parameters.Add(pPostDate)

                    con.Open()
                    cmd.ExecuteNonQuery()
                    Return True
                End Using
            Catch ex As Exception
                Return False
            End Try
        End Function

        Public Overrides Function GetArticlesByUserID(ByVal UserID As String) As System.Collections.Generic.List(Of Article)
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("Article_GetArticlesByUserID", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    Dim pUserID As New SqlParameter("@ID", SqlDbType.NVarChar)
                    pUserID.Value = UserID
                    cmd.Parameters.Add(pUserID)
                    con.Open()

                    Dim reader As IDataReader = cmd.ExecuteReader()
                    Return GetArticleCollectionFromReader(reader)

                End Using
            Catch ex As Exception
                Return New System.Collections.Generic.List(Of Article)
            End Try
        End Function

        Public Overrides Function GetArticlesOnCreationDate(ByVal ArticleDate As Date) As System.Collections.Generic.List(Of Article)
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("Article_GetArticlesOnCreationDate", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    Dim pCreationDate As New SqlParameter("@Date", SqlDbType.SmallDateTime)
                    pCreationDate.Value = ArticleDate
                    cmd.Parameters.Add(pCreationDate)
                    con.Open()

                    Dim reader As IDataReader = cmd.ExecuteReader()
                    Return GetArticleCollectionFromReader(reader)

                End Using
            Catch ex As Exception
                Return New System.Collections.Generic.List(Of Article)
            End Try
        End Function

        Public Overrides Function GetArticlesOnExpireDate(ByVal ArticleDate As Date) As System.Collections.Generic.List(Of Article)
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("Article_GetArticlesOnExpireDate", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    Dim pExpireDate As New SqlParameter("@Date", SqlDbType.SmallDateTime)
                    pExpireDate.Value = ArticleDate
                    cmd.Parameters.Add(pExpireDate)
                    con.Open()

                    Dim reader As IDataReader = cmd.ExecuteReader()
                    Return GetArticleCollectionFromReader(reader)

                End Using
            Catch ex As Exception
                Return New System.Collections.Generic.List(Of Article)
            End Try
        End Function

        Public Overrides Function GetArticlesOnPostDate(ByVal ArticleDate As Date) As System.Collections.Generic.List(Of Article)
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("Article_GetArticlesOnPostDate", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    Dim pPostDate As New SqlParameter("@Date", SqlDbType.SmallDateTime)
                    pPostDate.Value = ArticleDate
                    cmd.Parameters.Add(pPostDate)
                    con.Open()

                    Dim reader As IDataReader = cmd.ExecuteReader()
                    Return GetArticleCollectionFromReader(reader)

                End Using
            Catch ex As Exception
                Return New System.Collections.Generic.List(Of Article)
            End Try
        End Function

        Public Overrides Function GetNextID() As Integer
            Try
                Using con As New SqlConnection(Me.ConnectionString)
                    Dim cmd As New SqlCommand("Article_GetNextID", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    con.Open()
                    Dim dr As SqlDataReader
                    dr = cmd.ExecuteReader
                    Dim newId As Integer
                    dr.Read()
                    newId = CInt(dr.GetValue(0))
                    Return newId + 1
                End Using
            Catch ex As Exception
                Return 0
            End Try
        End Function

        Public Overrides Function GetCurrentArticles() As System.Collections.Generic.List(Of Article)
            Try
                Using con As New SqlConnection(Me.ConnectionString)

                    Dim cmd As New SqlCommand("Article_GetCurrentArticles", con)
                    cmd.CommandType = CommandType.StoredProcedure

                    Dim today As New SqlParameter("@Today", SqlDbType.SmallDateTime)
                    today.Value = Date.Today
                    cmd.Parameters.Add(today)
                    con.Open()
                    Dim reader As IDataReader = cmd.ExecuteReader()
                    Return GetArticleCollectionFromReader(reader)

                End Using
            Catch ex As Exception
                Return New System.Collections.Generic.List(Of Article)
            End Try
        End Function
    End Class
End Namespace

